import sqlite3
import csv

conn = sqlite3.connect('./game_db')
c = conn.cursor()

def table_to_sql(filename,tablename):
  values = csv.reader(open(filename))
  ret = {}
  header = values.next()[:]
  first_row = values.next()[:]
  query_string = []
  
  for name,value in zip(header,first_row):
    try:
      float(value)
      query_string.append(name+' real')
    except:
      query_string.append(name+' text')
  
  try:
    c.execute('drop table '+tablename)
  except:
    pass
  try:
    c.execute('create table '+tablename+'('+','.join(query_string)+')')
  except:
    print 'updating table '+tablename+' failed'
  
  table = csv.reader(open(filename))
  table.next()
  
  for row in table:
    values= []
    for val in row:
      try:
        val = float(val)
        values.append(str(val))
      except:
        values.append('\''+val+'\'')
    try:
      c.execute("""insert into """+tablename+""" values ("""+','.join(values)+""")""")
    except:
      print 'data missmatch in table '+tablename 

def sql_exec(command):
  return c.execute(command).fetchall()
def get_line(command):
  return toline(sql_exec(command))
  
def get_value(command):
  return get_line(command)[0]
  
def toline(line):
  return (zip(*line)[0])
  
# Create table
def update_tables():
  table_to_sql('data/companies.csv','companies')
  table_to_sql('data/platoons.csv','platoons')
  table_to_sql('data/fireteams.csv','fireteams')
  table_to_sql('data/vehicles.csv','vehicles')
  table_to_sql('data/soldiers.csv','soldiers')
  table_to_sql('data/weapons.csv','weapons')
  table_to_sql('data/terrains.csv','terrains')
  table_to_sql('data/graphics.csv','graphics')
  table_to_sql('data/sides.csv','sides')

update_tables()

